Now you can author some code that interacts with your EDM. Begin by updating your Program class to call a helper method from Main(), which will print out each item in the Inventory database using your conceptual model, and another which will insert a new record to the Inventory table:
class Program { static void Main(string[] args) { Console.WriteLine("***** Fun with ADO.NET EF *****"); AddNewRecord(); PrintAllInventory(); Console.ReadLine(); } private static void AddNewRecord() { // Add record to the Inventory table of the AutoLot // database. using (AutoLotEntities context = new AutoLotEntities()) { try { // Hard code data for a new record, for testing. context.Cars.AddObject(new Car() { CarID = 2222, Make = "Yugo", Color = "Brown" }); context.SaveChanges(); } catch(Exception ex) { Console.WriteLine(ex.InnerException.Message); } } } private static void PrintAllInventory() { // Select all items from the Inventory table of AutoLot, // and print out the data using our custom ToString() // of the Car entity class. using (AutoLotEntities context = new AutoLotEntities()) { foreach (Car c in context.Cars) Console.WriteLine(c); } } }
You have seen code similar to this earlier in the chapter, but now you should have a much better idea about how it works. Each helper method creates an instance of the ObjectContext derived class (AutoLotEntities), and uses the strongly typed Cars property to interact with the ObjectSet<Car> field. Enumerating each item exposed by the Cars property enables you to submit a SQL SELECT statement indirectly to the underlying ADO.NET data provider. By inserting a new Car object with the AddObject() method of ObjectSet<Car>, and then calling SaveChanges() on the context, you have preformed a SQL INSERT.
When you wish to remove a record from the database, you will first need to locate the correct item in the ObjectSet<T>, which you can find by passing an EntityKey object (which is a member of the System.Data namespace) to the GetObjectByKey() method. Assuming you have imported this namespace into your C# code file, you can now author the following helper method:
private static void RemoveRecord() { // Find a car to delete by primary key. using (AutoLotEntities context = new AutoLotEntities()) { // Define a key for the entity we are looking for. EntityKey key = new EntityKey("AutoLotEntities.Cars", "CarID", 2222); // See if we have it, and delete it if we do. Car carToDelete = (Car)context.GetObjectByKey(key); if (carToDelete != null) { context.DeleteObject(carToDelete); context.SaveChanges(); } } }
Note For better or for worse, calling GetObjectByKey() requires a roundtrip to the database before you can delete the object.
Notice that when you are creating an EntityKey object, you need use a string object to inform it which ObjectSet<T> to evaluate in a given ObjectContext derived class. The second argument is another string; this one represents the property name on the entity class that is marked as the key, and the final constructor argument is the value of the primary key. Once you find the object in question, you can call DeleteObject() off your context and save the changes.
Updating a record is also straightforward; locate the object you wish to change, set new property values on the returned entity, and save the changes:
private static void UpdateRecord() { // Find a car to delete by primary key. using (AutoLotEntities context = new AutoLotEntities()) { // Define a key for the entity we are looking for. EntityKey key = new EntityKey("AutoLotEntities.Cars", "CarID", 2222); // Grab the car, change it, save! Car carToUpdate = (Car)context.GetObjectByKey(key); if (carToUpdate != null) { carToUpdate.Color = "Blue"; context.SaveChanges(); } } }
The preceding method might seem a bit off, at least until you remember that the entity object returned from GetObjectByKey() is a reference to an existing object in the ObjectSet<T> field. Thus, when you set properties to change the state, you are changing the same object in memory.
Note Much like an ADO.NET DataRow object (see Chapter 22), any descendent of EntityObject (meaning, all of your entity classes) has a property named EntityState, which is used by the object context to determine whether the item has been modified, deleted, is detached, and so forth. This is set on your behalf as you work with the programming model; however, you can change this manually as required.
So far, you have been working with a few simple methods on the object context and entity objects to perform selections, inserts, updates, and deletes. This is useful all by itself; however, EF becomes much more powerful when you incorporate LINQ queries. If you wish to use LINQ to update or delete records, you don’t need to make an EntityKey object manually. Consider the following update to the RemoveRecord() method, which will not work as expected at this point:
private static void RemoveRecord() { // Find a car to delete by primary key. using (AutoLotEntities context = new AutoLotEntities()) { // See if we have it? var carToDelete = from c in context.Cars where c.CarID == 2222 select c; if (carToDelete != null) { context.DeleteObject(carToDelete); context.SaveChanges(); } } }
This code compiles, but you will receive a runtime exception when you attempt to call the DeleteObject() method. The reason: This particular LINQ query returns an ObjectQuery<T> object, not a Car object. Always remember that when you build a LINQ query that attempts to locate a single entity, you will get back an ObjectQuery<T> representing a query that is capable of bringing back the data you are looking for. To execute the query (and bring back the Car entity), you must execute a method such as FirstOrDefault() on the query object, as in the following example:
var carToDelete = (from c in context.Cars where c.CarID == 2222 select c).FirstOrDefault();
By calling FirstOrDefault() on the ObjectQuery<T>, you find the item you want; or, if there is no Car with the ID of 2222, a default value of null.
Given that you have already worked with many LINQ expressions in Chapter 13, a few more examples will suffice for the time being:
private static void FunWithLINQQueries() { using (AutoLotEntities context = new AutoLotEntities()) { // Get a projection of new data. var colorsMakes = from item in context.Cars select new { item.Color, item.Make }; foreach (var item in colorsMakes) { Console.WriteLine(item); } // Get only items where CarID < 1000 var idsLessThan1000 = from item in context.Cars where item.CarID < 1000 select item; foreach (var item in idsLessThan1000) { Console.WriteLine(item); } } }
While the syntax of these queries is simple enough, remember that you are hitting a database each time you apply a LINQ query to the object context! Recall that when you wish to obtain an independent copy of data, which can be the target of new LINQ queries, you want to use immediate execution with the ToList<T>(), ToArray<T>() or ToDictionary<K,V>() extension methods (among others). Here is an update of the previous method, which performs the equivalent of a SELECT *, caches the entities as an array, and manipulates the array data using LINQ to Objects:
using (AutoLotEntities context = new AutoLotEntities()) { // Get all data from the Inventory table. // could also write: // var allData = (from item in context.Cars select item).ToArray(); var allData = context.Cars.ToArray(); // Get a projection of new data. var colorsMakes = from item in allData select new { item.Color, item.Make }; // Get only items where CarID < 1000 var idsLessThan1000 = from item in allData where item.CarID < 1000 select item; }
Working with LINQ to Entities is much more enticing when your EDM contains multiple related tables. You’ll see some example that illustrate this in a moment; however, let’s wrap up this current example by looking at two other ways you can interact with your object context.
To be sure, you will be querying and ObjectSet<T> with LINQ a majority of the time. The entity client will break down your LINQ query into a fitting SQL statement, passing it onto the database for processing. However, you can use Entity SQL if you wish to have more control over how the query is formatted.
Entity SQL is a SQL-like query language that can be applied to entities. While the format of an Entity SQL statement is similar to a traditional SQL statement, it is not identical. Entity SQL has a unique syntax because the entities receive the query, not the physical database. Like a LINQ to Entities query, an Entity SQL query is used to pass a “real” SQL query to the database.
This chapter will not dive into the details of building Entity SQL commands, so please consult the .NET Framework 4.0 SDK documentation if you want more information. However, one example might be helpful. Consider the following method, which builds an Entity SQL statement that finds all black cars in the ObjectSet<Car> collection:
private static void FunWithEntitySQL() { using (AutoLotEntities context = new AutoLotEntities()) { // Build a string containing Entity SQL syntax. string query = "SELECT VALUE car FROM AutoLotEntities.Cars " + "AS car WHERE car.Color='black'"; // Now build a ObjectQuery<T> based on the string. var blackCars = context.CreateQuery<Car>(query); foreach (var item in blackCars) { Console.WriteLine(item); } } }
Notice that you pass in the formatted Entity SQL statement as an argument to the CreateQuery<T> method of your object context.
When you use LINQ to Entities or Entity SQL, the fetched data is mapped back to your entity classes automatically, thanks to the entity client service. Typically this is exactly what you require; however, you can intercept the result set before it makes its way to your entity objects and process it manually using the EntityDataReader.
Here is a final helper method for this example, which uses several members of the System.Data.EntityClient namespace to build a connection manually through a command object and data reader. This code should look familiar due to your previous work in Chapter 21; the major difference is that you use Entity SQL, rather than “normal” SQL:
private static void FunWithEntityDataReader() { // Make a connection object, based on our *.config file. using (EntityConnection cn = new EntityConnection("name=AutoLotEntities")) { cn.Open(); // Now build an Entity SQL query. string query = "SELECT VALUE car FROM AutoLotEntities.Cars AS car"; // Create a command object. using (EntityCommand cmd = cn.CreateCommand()) { cmd.CommandText = query; // Finally, get the data reader and process records. using (EntityDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess)) { while (dr.Read()) { Console.WriteLine("***** RECORD *****"); Console.WriteLine("ID: {0}", dr["CarID"]); Console.WriteLine("Make: {0}", dr["Make"]); Console.WriteLine("Color: {0}", dr["Color"]); Console.WriteLine("Pet Name: {0}", dr["CarNickname"]); Console.WriteLine(); } } } } }
Great! This initial example should go a long way toward helping you understand the nuts and bolts of working with the Entity Framework. As mentioned previously, things become much more interesting when your EDM contains interrelated tables, which you will learn about next.
Source Code You can find the InventoryEDMConsoleApp example under the Chapter 23 subdirectory.